/**
 * 
 */
package org.vsg.stock.basic.persistence.hibernate;

import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;
import java.util.Map.Entry;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.vsg.common.hibernate.AbstractGenericDaoImpl;
import org.vsg.stock.basic.domain.StockCategory;
import org.vsg.stock.basic.persistence.StockCategoryDao;

/**
 * @author vison
 *
 */
public class StockCategoryDaoImpl extends AbstractGenericDaoImpl implements
	StockCategoryDao {

	/* (non-Javadoc)
	 * @see org.vsg.stock.basic.persistence.StockDailyLineDao#saveAll(java.util.Collection)
	 */
	@Override
	public void saveAll(Collection<StockCategory> entities)
			throws DataAccessException {
		// TODO Auto-generated method stub
		this.getTemplate().saveOrUpdateAll(entities);
		logger.debug("update and save entity " );
	}

	@Override
	public List<StockCategory> findAllCategory() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity ");
		query.append("FROM ").append(StockCategory.class.getName());
		query.append(" as entity ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<StockCategory> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		StockCategory entity = null;
		List<StockCategory> entiyies = new Vector<StockCategory>();
		
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		
		return entiyies;
	}

	@Override
	public List<String> findAllDescription() throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity.description ");
		query.append("FROM ").append(StockCategory.class.getName());
		query.append(" as entity ");

		Collection params = new Vector();
		
		logger.debug("query : " + query.toString());
		
		Iterator<String> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		List<String> entiyies = new Vector<String>();
		
		String entity = null;
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		return entiyies;
	}

	@Override
	public List<String> findDescriptionByStockCode(String stockCode)
			throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity.description ");
		query.append("FROM ").append(StockCategory.class.getName()).append(" as entity ");
		query.append("JOIN entity.assoStockInfos as stockInfo ");
		query.append("WHERE stockInfo.code = ? ");

		Collection params = new Vector();
		params.add(stockCode);
		
		logger.debug("query : " + query.toString());
		
		Iterator<String> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		List<String> entiyies = new Vector<String>();
		
		String entity = null;
		while ( iter.hasNext() ) {
			entity = iter.next();
			entiyies.add(entity );
		}
		return entiyies;
	}

	@Override
	public StockCategory getCategoryByName(String name)
			throws DataAccessException {
		// TODO Auto-generated method stub
		StringBuilder query = new StringBuilder("SELECT ");
		query.append("entity ");
		query.append("FROM ").append(StockCategory.class.getName()).append(" as entity ");
		query.append("WHERE entity.description = ? ");
		
		Collection params = new Vector();
		params.add( name );
		
		logger.debug("query : " + query.toString());
		
		Iterator<StockCategory> iter = getTemplate().iterate(query.toString() , params.toArray());
		
		StockCategory entity = null;
		if ( iter.hasNext() ) {

			entity = iter.next();
		}
		

		
		return entity;
	}

	@Override
	public void cleanAll() throws DataAccessException {
		// TODO Auto-generated method stub
		HibernateCallback action = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query query = sess.createSQLQuery("truncate table STOCK_CATEGORY");
				int result = query.executeUpdate();
				return new Integer(result);
			}
			
		};
		
		getTemplate().execute(action);
		
		logger.info("clean up StockCategory data");		
	}

	@Override
	public void cleanAllAndMapping() throws DataAccessException {
		// TODO Auto-generated method stub
		HibernateCallback action = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query query = sess.createSQLQuery("truncate table STOCK_CATEGORY_MAP");
				int result = query.executeUpdate();
				return new Integer(result);
			}
			
		};
		
		getTemplate().execute(action);
		
		
		cleanAll();
		
		logger.info("clean up StockCategory And Mapping data");				
	}

	@Override
	public void updateToStockInfoMapping(final String cateDesc, final String stockCode)
			throws DataAccessException {
		// TODO Auto-generated method stub
		
		HibernateCallback action = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				StringBuilder queryStr = new StringBuilder("INSERT INTO ");
				queryStr.append("stock_category_map (stock_code , id_category) ");
				queryStr.append("SELECT  ");
				queryStr.append("'"+stockCode+"'").append(",").append("stkCate.id ");
				queryStr.append("FROM stock_category AS stkCate ");
				queryStr.append("WHERE stkCate.description = ? ");
				queryStr.append("AND stkCate.id NOT IN (");
				queryStr.append("SELECT map.id_category FROM stock_info " +
						"LEFT OUTER JOIN stock_category_map AS map " +
						"ON stock_info.stock_code = map.stock_code " +
						"WHERE stock_info.stock_code = ? " +
						"AND stock_info.stock_code is not null " +
						"AND map.id_category is not null").append(")");
				
				Query query = sess.createSQLQuery(queryStr.toString());
				query.setString(0,cateDesc);
				query.setString(1, stockCode);
				
				int result = query.executeUpdate();
		
				return new Integer(result);
			}
			
		};
		
		Integer result = (Integer)getTemplate().execute(action);

	}

	@Override
	public void updateBatchToStockInfoMapping(
			Map<String, List<String>> cateStocksMap) throws DataAccessException {
		// TODO Auto-generated method stub
		final Set<Map.Entry<String,List<String>>> entrySet = cateStocksMap.entrySet();

		HibernateCallback action = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session sess)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
/*				StringBuilder queryStr = new StringBuilder("INSERT INTO ");
				queryStr.append("stock_category_map (stock_code , id_category) ");
				queryStr.append("SELECT  ");
				queryStr.append("? ").append(",").append("stkCate.id ");
				queryStr.append("FROM stock_category AS stkCate ");
				queryStr.append("WHERE stkCate.description = ? ");
				queryStr.append("AND stkCate.id NOT IN (");
				queryStr.append("SELECT map.id_category FROM stock_info " +
						"LEFT OUTER JOIN stock_category_map AS map " +
						"ON stock_info.stock_code = map.stock_code " +
						"WHERE stock_info.stock_code = ? " +
						"AND stock_info.stock_code is not null " +
						"AND map.id_category is not null").append(")");*/
				
				StringBuilder queryStr = new StringBuilder("INSERT INTO ");
				queryStr.append("stock_category_map (stock_code , id_category) ");
				queryStr.append("SELECT  ");
				queryStr.append("? ").append(",").append("stkCate.id ");
				queryStr.append("FROM stock_category AS stkCate ");
				queryStr.append("WHERE stkCate.description = ? ");
				queryStr.append("AND stkCate.id NOT IN (");
				queryStr.append("SELECT map.id_category FROM stock_category_map AS map "+
						"WHERE map.stock_code = ? " +
						"AND map.id_category is not null").append(")");

				int result = 0;
				int count=0;

				Entry<String,List<String>> entry = null;
				for (Iterator<Map.Entry<String,List<String>>> iterEntrySet = entrySet.iterator() ;  iterEntrySet.hasNext(); ) {
					entry = iterEntrySet.next();
					
					String cateDesc = entry.getKey();
					List<String> stkCodes = entry.getValue();
					
					String stockCode = null;
					for (Iterator<String> stkCodeIter = stkCodes.iterator() ; stkCodeIter.hasNext(); ) {
						stockCode = stkCodeIter.next();
						Query query = sess.createSQLQuery(queryStr.toString());
						query.setString(0, stockCode);
						query.setString(1,cateDesc);
						query.setString(2, stockCode);
						result = query.executeUpdate();

					}
					
					iterEntrySet.remove();
				}				

				// commit result object 

				return new Integer(result);
			}
			
		};
		
		Integer result = (Integer)getTemplate().execute(action);		
		
	}

	

}
